"""
Trend analysis service for device metrics

Provides time-based trend analysis:
- Hourly/Daily aggregations
- Moving averages
- Trend detection (increasing/decreasing/stable)
"""

from __future__ import annotations

import logging
from datetime import datetime, timedelta
from typing import Any, Literal

from sqlalchemy import text
from sqlalchemy.orm import Session

logger = logging.getLogger(__name__)


TrendDirection = Literal["increasing", "decreasing", "stable"]
AggregationInterval = Literal["hour", "day"]


class TrendService:
    """
    Trend analysis service

    Provides time-series trend analysis for device metrics
    using pure SQL aggregations.
    """

    def __init__(self, session: Session) -> None:
        self.session = session

    def get_time_series(
        self,
        device_id: int,
        metric_name: str,
        interval: AggregationInterval = "hour",
        hours: int = 24,
    ) -> list[dict[str, Any]]:
        """
        Get time-series data aggregated by interval

        Args:
            device_id: Device ID
            metric_name: Metric name
            interval: Aggregation interval ('hour' or 'day')
            hours: Time window in hours

        Returns:
            List of aggregated data points
        """
        start_time = datetime.utcnow() - timedelta(hours=hours)

        # SQLite datetime functions: strftime for grouping
        if interval == "hour":
            # Group by hour: YYYY-MM-DD HH:00:00
            time_format = "%Y-%m-%d %H:00:00"
        else:  # day
            # Group by day: YYYY-MM-DD 00:00:00
            time_format = "%Y-%m-%d 00:00:00"

        query = """
        SELECT
            strftime(:time_format, timestamp) as time_bucket,
            COUNT(*) as count,
            AVG(value) as avg_value,
            MIN(value) as min_value,
            MAX(value) as max_value
        FROM device_metrics
        WHERE device_id = :device_id
          AND metric_name = :metric_name
          AND timestamp >= :start_time
        GROUP BY time_bucket
        ORDER BY time_bucket ASC
        """

        result = self.session.execute(
            text(query),
            {
                "time_format": time_format,
                "device_id": device_id,
                "metric_name": metric_name,
                "start_time": start_time,
            },
        )

        rows = result.fetchall()

        return [
            {
                "timestamp": row[0],
                "count": row[1],
                "avg_value": row[2],
                "min_value": row[3],
                "max_value": row[4],
            }
            for row in rows
        ]

    def get_moving_average(
        self,
        device_id: int,
        metric_name: str,
        window_size: int = 10,
        hours: int = 24,
    ) -> list[dict[str, Any]]:
        """
        Calculate moving average for metric

        Args:
            device_id: Device ID
            metric_name: Metric name
            window_size: Number of data points in moving average window
            hours: Time window in hours

        Returns:
            List of data points with moving averages
        """
        start_time = datetime.utcnow() - timedelta(hours=hours)

        # Get raw data points
        query = """
        SELECT
            timestamp,
            value
        FROM device_metrics
        WHERE device_id = :device_id
          AND metric_name = :metric_name
          AND timestamp >= :start_time
        ORDER BY timestamp ASC
        """

        result = self.session.execute(
            text(query),
            {
                "device_id": device_id,
                "metric_name": metric_name,
                "start_time": start_time,
            },
        )

        rows = result.fetchall()

        if len(rows) < window_size:
            # Not enough data for moving average
            return [
                {
                    "timestamp": row[0],
                    "value": row[1],
                    "moving_average": None,
                }
                for row in rows
            ]

        # Calculate moving average
        data_points = []
        values = [row[1] for row in rows]

        for i, row in enumerate(rows):
            if i < window_size - 1:
                # Not enough previous points yet
                moving_avg = None
            else:
                # Calculate average of window_size previous points
                window_values = values[i - window_size + 1 : i + 1]
                moving_avg = sum(window_values) / len(window_values)

            data_points.append(
                {
                    "timestamp": row[0],
                    "value": row[1],
                    "moving_average": moving_avg,
                }
            )

        return data_points

    def detect_trend(
        self,
        device_id: int,
        metric_name: str,
        hours: int = 24,
        threshold_percent: float = 5.0,
    ) -> dict[str, Any]:
        """
        Detect trend direction (increasing/decreasing/stable)

        Args:
            device_id: Device ID
            metric_name: Metric name
            hours: Time window in hours
            threshold_percent: Threshold for trend detection (% change)

        Returns:
            Trend analysis with direction and statistics
        """
        start_time = datetime.utcnow() - timedelta(hours=hours)

        # Get first and last values
        query = """
        SELECT
            MIN(timestamp) as first_time,
            MAX(timestamp) as last_time,
            (SELECT value FROM device_metrics
             WHERE device_id = :device_id AND metric_name = :metric_name
             AND timestamp >= :start_time
             ORDER BY timestamp ASC LIMIT 1) as first_value,
            (SELECT value FROM device_metrics
             WHERE device_id = :device_id AND metric_name = :metric_name
             AND timestamp >= :start_time
             ORDER BY timestamp DESC LIMIT 1) as last_value,
            AVG(value) as avg_value,
            COUNT(*) as count
        FROM device_metrics
        WHERE device_id = :device_id
          AND metric_name = :metric_name
          AND timestamp >= :start_time
        """

        result = self.session.execute(
            text(query),
            {
                "device_id": device_id,
                "metric_name": metric_name,
                "start_time": start_time,
            },
        )

        row = result.fetchone()

        if not row or row[5] == 0:
            return {
                "device_id": device_id,
                "metric_name": metric_name,
                "time_window_hours": hours,
                "trend_direction": "stable",
                "change_percent": 0.0,
                "first_value": None,
                "last_value": None,
                "avg_value": None,
                "data_points": 0,
            }

        first_value = row[2]
        last_value = row[3]
        avg_value = row[4]
        count = row[5]

        # Calculate percent change
        if first_value == 0:
            # Avoid division by zero
            if last_value == 0:
                change_percent = 0.0
            else:
                change_percent = 100.0  # Arbitrary large change
        else:
            change_percent = ((last_value - first_value) / first_value) * 100

        # Determine trend direction
        if abs(change_percent) < threshold_percent:
            direction: TrendDirection = "stable"
        elif change_percent > 0:
            direction = "increasing"
        else:
            direction = "decreasing"

        return {
            "device_id": device_id,
            "metric_name": metric_name,
            "time_window_hours": hours,
            "trend_direction": direction,
            "change_percent": round(change_percent, 2),
            "first_value": first_value,
            "last_value": last_value,
            "avg_value": avg_value,
            "data_points": count,
            "threshold_percent": threshold_percent,
        }

    def get_rate_of_change(
        self,
        device_id: int,
        metric_name: str,
        interval_minutes: int = 60,
        hours: int = 24,
    ) -> list[dict[str, Any]]:
        """
        Calculate rate of change over time

        Args:
            device_id: Device ID
            metric_name: Metric name
            interval_minutes: Interval for rate calculation (minutes)
            hours: Time window in hours

        Returns:
            List of rate of change data points
        """
        start_time = datetime.utcnow() - timedelta(hours=hours)

        # Get data points
        query = """
        SELECT
            timestamp,
            value
        FROM device_metrics
        WHERE device_id = :device_id
          AND metric_name = :metric_name
          AND timestamp >= :start_time
        ORDER BY timestamp ASC
        """

        result = self.session.execute(
            text(query),
            {
                "device_id": device_id,
                "metric_name": metric_name,
                "start_time": start_time,
            },
        )

        rows = result.fetchall()

        if len(rows) < 2:
            return []

        # Calculate rate of change between consecutive points
        rate_data = []
        interval_seconds = interval_minutes * 60

        for i in range(1, len(rows)):
            prev_time = datetime.fromisoformat(rows[i - 1][0]) if isinstance(rows[i - 1][0], str) else rows[i - 1][0]
            curr_time = datetime.fromisoformat(rows[i][0]) if isinstance(rows[i][0], str) else rows[i][0]
            prev_value = rows[i - 1][1]
            curr_value = rows[i][1]

            # Calculate time difference in seconds
            time_diff = (curr_time - prev_time).total_seconds()

            if time_diff > 0:
                # Calculate rate per interval
                value_change = curr_value - prev_value
                rate_per_interval = (value_change / time_diff) * interval_seconds

                rate_data.append(
                    {
                        "timestamp": rows[i][0],
                        "value": curr_value,
                        "rate_of_change": round(rate_per_interval, 4),
                        "time_diff_seconds": time_diff,
                    }
                )

        return rate_data
